<?php
/**
 * Database Migration Script
 * Rename tables to use unified gf_ prefix
 * 
 * Usage: php rename_tables_gf_prefix.php
 * 
 * This script uses the same database configuration as your webman application
 */

// Load environment variables from .env file if it exists
$envFile = __DIR__ . '/../../.env';
if (file_exists($envFile)) {
    $lines = file($envFile, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
    foreach ($lines as $line) {
        if (strpos($line, '=') !== false && strpos($line, '#') !== 0) {
            list($key, $value) = explode('=', $line, 2);
            $_ENV[trim($key)] = trim($value);
        }
    }
}

// Database configuration from webman config
try {
    // Get database configuration from environment variables or use defaults
    $host = $_ENV['DB_HOST'] ?? '127.0.0.1';
    $port = $_ENV['DB_PORT'] ?? 3306;
    $dbname = $_ENV['DB_DATABASE'] ?? 'XappTool';
    $username = $_ENV['DB_USERNAME'] ?? 'root';
    $password = $_ENV['DB_PASSWORD'] ?? '';
    
    echo "Connecting to database: $host:$port/$dbname\n";
    echo "Using username: $username\n\n";
    
    $pdo = new PDO("mysql:host=$host;port=$port;dbname=$dbname;charset=utf8mb4", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    echo "✅ Database connection established successfully.\n\n";
    
    // Tables that need to be renamed (old_name => new_name)
    $tablesToRename = [
        'device' => 'gf_device',
        'antifraud_high_risk' => 'gf_antifraud_high_risk',
        'invitations' => 'gf_invitations'
    ];
    
    $successCount = 0;
    $errorCount = 0;
    $skippedCount = 0;
    
    foreach ($tablesToRename as $oldName => $newName) {
        echo "Processing table: $oldName → $newName\n";
        
        try {
            // Check if old table exists
            $checkStmt = $pdo->prepare("SHOW TABLES LIKE ?");
            $checkStmt->execute([$oldName]);
            $oldTableExists = $checkStmt->fetch();
            
            if (!$oldTableExists) {
                echo "  ⚠️  Table '$oldName' does not exist, skipping...\n";
                $skippedCount++;
                continue;
            }
            
            // Check if new table already exists
            $checkStmt = $pdo->prepare("SHOW TABLES LIKE ?");
            $checkStmt->execute([$newName]);
            $newTableExists = $checkStmt->fetch();
            
            if ($newTableExists) {
                echo "  ⚠️  Table '$newName' already exists, skipping rename from '$oldName'...\n";
                $skippedCount++;
                continue;
            }
            
            // Rename table
            $renameSql = "RENAME TABLE `$oldName` TO `$newName`";
            $pdo->exec($renameSql);
            
            echo "  ✅ Successfully renamed table '$oldName' to '$newName'\n";
            $successCount++;
            
        } catch (PDOException $e) {
            echo "  ❌ Error renaming table '$oldName' to '$newName': " . $e->getMessage() . "\n";
            $errorCount++;
        }
        
        echo "\n";
    }
    
    echo "================ MIGRATION SUMMARY ================\n";
    echo "✅ Successfully renamed: $successCount tables\n";
    echo "⚠️  Skipped: $skippedCount tables\n";
    echo "❌ Errors encountered: $errorCount tables\n";
    echo "==================================================\n\n";
    
    // Additional verification - list all tables with gf_ prefix
    echo "📋 All tables with gf_ prefix:\n";
    echo "--------------------------------------------------\n";
    $stmt = $pdo->query("SHOW TABLES LIKE 'gf_%'");
    $gfTables = $stmt->fetchAll(PDO::FETCH_COLUMN);
    
    if (empty($gfTables)) {
        echo "No tables found with gf_ prefix.\n";
    } else {
        foreach ($gfTables as $table) {
            echo "- $table\n";
        }
    }
    
    echo "\n";
    echo "🎯 Migration completed! All database tables now use the unified gf_ prefix.\n";
    
} catch (PDOException $e) {
    echo "❌ Database connection failed: " . $e->getMessage() . "\n";
    echo "\n💡 Troubleshooting tips:\n";
    echo "1. Check if MySQL is running: brew services list (macOS) or systemctl status mysql (Linux)\n";
    echo "2. Verify database credentials in your .env file\n";
    echo "3. Check if the database '$dbname' exists\n";
    echo "4. Ensure MySQL is accepting connections on $host:$port\n";
    exit(1);
}

?>